package org.testtools.handler;

import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.testtools.assertion.AssertThat;
import org.testtools.connector.impl.JDBCConnector;

public class DBHandler {

	private JDBCConnector connector;

	public DBHandler(String driver, String url, String username, String password) {
		connector = new JDBCConnector(driver, url, username, password, true);
	}

	public synchronized static String executeQuery(Connection conn, String sql,
			String field) throws SQLException, IOException {
		return executeQuery(conn, sql, field, 1);
	}

	public synchronized static List<String[]> executeQuery(Connection conn,
			String sql, boolean metadata) throws SQLException, IOException {
		if (AssertThat.isNull(conn, sql))
			return null;

		final Statement ps = conn.createStatement();
		final ResultSet rs = ps.executeQuery(sql);
		final List<String[]> result = getResultSetValues(rs, metadata);

		rs.close();
		ps.close();

		return result;
	}

	public synchronized static List<String[]> executeQuery(Connection conn,
			String sql) throws SQLException, IOException {
		return executeQuery(conn, sql, true);
	}

	private static List<String[]> getResultSetValues(ResultSet rs,
			boolean metadata) throws SQLException, IOException {
		List<String[]> result = new ArrayList<String[]>();
		int columnCount = rs.getMetaData().getColumnCount();
		String[] columnValues = new String[columnCount];

		if (metadata) {
			for (int i = 0; i < columnCount; i++) {
				columnValues[i] = rs.getMetaData().getColumnName(i + 1);
			}

			result.add(columnValues);
		}

		while (rs.next()) {
			columnValues = new String[columnCount];

			for (int i = 0; i < columnCount; i++) {
				columnValues[i] = getResult(i + 1, rs);
			}

			result.add(columnValues);
		}
		return result;
	}

	// TODO Move this to a Formatter Class
	public static String getResultInCSV(List<String[]> list) {
		return getResultInCSV(list, null);
	}

	// TODO Move this to a Formatter Class
	public static String getResultInCSV(List<String[]> list, String separator) {
		final StringBuffer sb = new StringBuffer(list.size() * 10);

		for (String[] strings : list) {
			for (int i = 0; i < strings.length; i++)
				sb.append(strings[i]).append(
						separator != null ? separator : ';');

			sb.append('\n');
		}

		return sb.substring(0, sb.length() - 1);
	}

	public synchronized static String executeQuery(Connection conn, String sql,
			String field, int line) throws SQLException, IOException {
		if (AssertThat.isNullOrEmpty(field, conn, sql) || line < 1)
			return null;

		final Statement ps = conn.createStatement();
		ResultSet rs = ps.executeQuery(sql);

		if (line == 1) {
			if (!rs.next())
				return null;
		} else {
			int ind = 1;
			while (ind <= line && rs.next())
				ind++;

			if (line != ind - 1)
				return null;
		}

		String result = getResult(field, rs);

		rs.close();
		ps.close();

		return result;
	}

	public synchronized static int executeUpdate(Connection conn, String sql)
			throws SQLException {
		Statement ps = null;

		try {
			ps = conn.createStatement();
			return ps.executeUpdate(sql);
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	private static String getClob(Object obj) throws SQLException, IOException {
		final StringBuffer sb = new StringBuffer();
		final Reader reader = ((Clob) obj).getCharacterStream();
		int ch = -1;

		while ((ch = reader.read()) != -1) {
			sb.append((char) ch);
		}

		return sb.toString();
	}

	private static String getResult(int field, ResultSet rs)
			throws SQLException, IOException {
		final Object obj = rs.getObject(field);

		if (obj instanceof Clob)
			return getClob(obj);
		else
			return rs.getString(field);
	}

	private static String getResult(String field, ResultSet rs)
			throws SQLException, IOException {
		final Object obj = rs.getObject(field);

		if (obj instanceof Clob)
			return getClob(obj);
		else
			return rs.getString(field);
	}

	public synchronized List<String[]> executeQuery(String sql)
			throws Exception {
		return executeQuery(sql, true);
	}

	public synchronized List<String[]> executeQuery(String sql, boolean metadata)
			throws Exception {

		final Connection conn = connector.getConnection();

		try {
			return executeQuery(conn, sql, metadata);
		} finally {
			conn.close();
		}
	}

	public synchronized String executeQuery(String sql, String field)
			throws Exception {

		final Connection conn = connector.getConnection();

		try {
			return executeQuery(conn, sql, field);
		} finally {
			conn.close();
		}
	}

	public synchronized String executeQuery(String sql, String field, int line)
			throws Exception {

		final Connection conn = connector.getConnection();

		try {
			return executeQuery(conn, sql, field, line);
		} finally {
			conn.close();
		}
	}

	public int executeUpdate(String sql) throws Exception {

		final Connection conn = connector.getConnection();

		try {
			return executeUpdate(connector.getConnection(), sql);
		} finally {
			conn.close();
		}
	}
}
